const connection = require('../../database/connection')

module.exports = {
    // 系统管理
    async getLoginUser(account) {
        const [[loginUser]] = await connection.execute('SELECT u.*, r.`name` role_name, r.`id` role_id FROM users u JOIN user_role_rels urr ON u.`id` = urr.`user_id` INNER JOIN roles r ON r.id = urr.`role_id` WHERE u.account = "' + account + '" AND urr.`deleted_at` IS NULL AND u.`deleted_at` IS NULL AND r.`deleted_at` IS NULL;')
        return loginUser
    },
    async resetUserPassword(id, password) {
        const [user] = await connection.execute('UPDATE `users` SET `password` = "' + password + '", `updated_at` = NOW() WHERE `id` = "' + id + '";')
        return user
    },
    async addUser(data) { // 增用户
        const [user] = await connection.execute('INSERT INTO `users` (`name`, `account`, `password`, `created_at`) VALUES ("' + data.name + '", "' + data.account + '", "' + data.password + '", NOW());')
        return user
    },
    async addRole(data) { // 增角色
        const [rel] = await connection.execute('INSERT INTO `roles` (`name`, `alias`, `created_at`) VALUES ("' + data.name + '", "' + data.alias + '", NOW());')
        return rel
    },
    async addUserRoleRel(data) { // 增用户角色关联表
        const [rel] = await connection.execute('INSERT INTO `user_role_rels` (`user_id`, `role_id`, `created_at`) VALUES ("' + data.user_id + '", "' + data.role_id + '", NOW());')
        return rel
    },
    async addRoleActionRel(data) { // 增角色权限关联表
        const [rel] = await connection.execute('INSERT INTO `role_action_rels` (`role_id`, `action_id`, `created_at`) VALUES ("' + data.role_id + '", "' + data.action_id + '", NOW());')
        return rel
    },
    async rmUserById(id) { // 删用户
        const [user] = await connection.execute('UPDATE `users` u SET u.`deleted_at` = NOW() WHERE u.`id` = "' + id + '";')
        return user
    },
    async rmRoleById(id) { // 删角色
        const [role] = await connection.execute('UPDATE `roles` r SET r.`deleted_at` = NOW() WHERE r.`id` = "' + id + '";')
        return role
    },
    async rmRoleActionRel(id) { // 删角色权限关联表
        const [rel] = await connection.execute('UPDATE `role_action_rels` rar SET rar.`deleted_at` = NOW() WHERE rar.`role_id` = "' + id + '";')
        return rel
    },
    async rmUserRoleRel(id) { // 删用户角色关联表
        const [rel] = await connection.execute('UPDATE `user_role_rels` urr SET urr.`deleted_at` = NOW() WHERE urr.`user_id` = "' + id + '";')
        return rel
    },
    async getUserById(id) { // 查用户
        const [[user]] = await connection.execute('SELECT u.* FROM `users` u WHERE u.`id` = "' + id + '" AND u.`deleted_at` IS NULL;')
        return user
    },
    async getRoleById(id) { // 查角色
        const [[role]] = await connection.execute('SELECT r.* FROM `roles` r WHERE r.`id` = "' + id + '" AND r.`deleted_at` IS NULL;')
        return role
    },
    async getUserByAccount(data) { // 查用户
        const [[user]] = await connection.execute('SELECT u.* FROM `users` u WHERE u.`account` = "' + data + '" AND u.`deleted_at` IS NULL;')
        return user
    },
    async getRoleByAlias(data) { // 查角色
        const [[role]] = await connection.execute('SELECT r.* FROM `roles` r WHERE r.`alias` = "' + data + '" AND r.`deleted_at` IS NULL;')
        return role
    },
    async getRoleActionByRoleId(id) {
        const [rel] = await connection.execute('SELECT rar.* FROM `role_action_rels` rar WHERE rar.`role_id` = "' + id + '" AND rar.`deleted_at` IS NULL;')
        return rel
    },
    async getRoleByName(data) { // 查角色
        const [[role]] = await connection.execute('SELECT r.* FROM `roles` r WHERE r.`name` = "' + data + '" AND r.`deleted_at` IS NULL;')
        return role
    },
    async getRoleList() { // 获取角色列表
        const [roleList] = await connection.execute('SELECT r.`id`, r.`name`, r.`alias` FROM `roles` r WHERE r.`deleted_at` IS NULL;')
        return roleList
    },
    async getActions() { // 获取角色权限
        const [actions] = await connection.execute('SELECT a.* FROM `actions` a WHERE a.`deleted_at` IS NULL;')
        return actions
    },
    async getActionById(id) {
        const [actions] = await connection.execute('SELECT a.* FROM `actions` a WHERE a.`id` = "' + id + '" AND a.`deleted_at` IS NULL;')
        return actions
    },
    async getUsers() { // 获取用户
        const [users] = await connection.execute('SELECT u.*, r.`name` role_name, r.`alias` role_alias FROM users u, roles r, user_role_rels urr WHERE u.id = urr.user_id AND urr.role_id = r.id AND u.`deleted_at` IS NULL AND r.`deleted_at` IS NULL AND urr.`deleted_at` IS NULL;')
        return users
    },
    async getRoles() { // 获取角色
        const [role] = await connection.execute('SELECT r.*, GROUP_CONCAT( a.alias SEPARATOR "," ) action_name FROM roles r, actions a, role_action_rels rar WHERE r.id = rar.role_id AND rar.action_id = a.id AND r.`deleted_at` IS NULL AND a.`deleted_at` IS NULL AND rar.`deleted_at` IS NULL GROUP BY r.id;')
        return role
    },
    async getUserRoleRel(id) { // 获取用户角色关联表
        const [rel] = await connection.execute('SELECT urr.* FROM `user_role_rels` urr WHERE urr.`role_id` = "' + id + '" AND urr.`deleted_at` IS NULL;')
        return rel
    },
    async getActionByAlias(data) { // 获取角色权限
        const [[ation]] = await connection.execute('SELECT a.* FROM `actions` a WHERE a.`alias` = "' + data + '" AND a.`deleted_at` IS NULL;')
        return ation
    },
    async updateRole(id, data) { // 改角色
        const [role] = await connection.execute('UPDATE `roles` SET `name` = "' + data.name + '", `alias` = "' + data.alias + '", `updated_at` = NOW() WHERE `id` = "' + id + '";')
        return role
    },
    async updateUser(id, data) { // 改用户
        const [user] = await connection.execute('UPDATE `users` SET `name` = "' + data.name + '", `updated_at` = NOW() WHERE `id` = "' + id + '";')
        return user
    },
    async updatePassword(id, data) { // 改用户
        const [user] = await connection.execute('UPDATE `users` SET `password` = "' + data.password + '", `updated_at` = NOW() WHERE `id` = "' + id + '";')
        return user
    },
};

